import { query } from '../../config/db.js';

/**
 * 邮箱验证码数据模型
 */
export class EmailVerificationCode {
  constructor(codeData) {
    this.id = codeData.id;
    this.email = codeData.email;
    this.code = codeData.code;
    this.type = codeData.type;
    this.expires_at = codeData.expires_at;
    this.is_used = codeData.is_used;
    this.created_at = codeData.created_at;
  }

  /**
   * 生成随机验证码
   * @param {number} length - 验证码长度
   * @returns {string} 验证码
   */
  static generateCode(length = 6) {
    const chars = '0123456789';
    let result = '';
    for (let i = 0; i < length; i++) {
      result += chars.charAt(Math.floor(Math.random() * chars.length));
    }
    return result;
  }

  /**
   * 创建新的验证码
   * @param {Object} codeData - 验证码数据
   * @returns {Promise<Object>} 创建的验证码信息
   */
  static async create(codeData) {
    const { email, type = 'register', expiresInMinutes = 10 } = codeData;
    
    // 生成验证码
    const code = EmailVerificationCode.generateCode();
    
    // 计算过期时间
    const expiresAt = new Date();
    expiresAt.setMinutes(expiresAt.getMinutes() + expiresInMinutes);
    
    const sql = `
      INSERT INTO email_verification_codes (email, code, type, expires_at)
      VALUES (?, ?, ?, ?)
    `;
    
    try {
      const result = await query(sql, [email, code, type, expiresAt]);
      return await EmailVerificationCode.findById(result.insertId);
    } catch (error) {
      console.error('创建验证码失败:', error);
      throw error;
    }
  }

  /**
   * 根据ID查找验证码
   * @param {number} id - 验证码ID
   * @returns {Promise<Object|null>} 验证码信息或null
   */
  static async findById(id) {
    const sql = `
      SELECT * FROM email_verification_codes WHERE id = ?
    `;
    
    try {
      const results = await query(sql, [id]);
      return results.length > 0 ? new EmailVerificationCode(results[0]) : null;
    } catch (error) {
      console.error('查找验证码失败:', error);
      throw error;
    }
  }

  /**
   * 根据邮箱和类型查找最新的有效验证码
   * @param {string} email - 邮箱地址
   * @param {string} type - 验证码类型
   * @returns {Promise<Object|null>} 验证码信息或null
   */
  static async findLatestByEmailAndType(email, type = 'register') {
    const sql = `
      SELECT * FROM email_verification_codes 
      WHERE email = ? AND type = ? AND is_used = FALSE AND expires_at > NOW()
      ORDER BY created_at DESC 
      LIMIT 1
    `;
    
    try {
      const results = await query(sql, [email, type]);
      return results.length > 0 ? new EmailVerificationCode(results[0]) : null;
    } catch (error) {
      console.error('查找最新验证码失败:', error);
      throw error;
    }
  }

  /**
   * 验证验证码
   * @param {string} email - 邮箱地址
   * @param {string} code - 验证码
   * @param {string} type - 验证码类型
   * @returns {Promise<boolean>} 验证是否成功
   */
  static async verify(email, code, type = 'register') {
    const sql = `
      SELECT * FROM email_verification_codes 
      WHERE email = ? AND code = ? AND type = ? AND is_used = FALSE AND expires_at > NOW()
      ORDER BY created_at DESC 
      LIMIT 1
    `;
    
    try {
      const results = await query(sql, [email, code, type]);
      
      if (results.length === 0) {
        return false;
      }

      // 标记验证码为已使用
      const verificationCode = results[0];
      await query(
        'UPDATE email_verification_codes SET is_used = TRUE WHERE id = ?',
        [verificationCode.id]
      );

      return true;
    } catch (error) {
      console.error('验证验证码失败:', error);
      throw error;
    }
  }

  /**
   * 检查邮箱是否可以发送新的验证码（防止频繁发送）
   * @param {string} email - 邮箱地址
   * @param {string} type - 验证码类型
   * @param {number} intervalMinutes - 发送间隔（分钟）
   * @returns {Promise<boolean>} 是否可以发送
   */
  static async canSendNewCode(email, type = 'register', intervalMinutes = 1) {
    const sql = `
      SELECT COUNT(*) as count FROM email_verification_codes 
      WHERE email = ? AND type = ? AND created_at > DATE_SUB(NOW(), INTERVAL ? MINUTE)
    `;
    
    try {
      const results = await query(sql, [email, type, intervalMinutes]);
      return results[0].count === 0;
    } catch (error) {
      console.error('检查发送间隔失败:', error);
      throw error;
    }
  }

  /**
   * 清理过期的验证码
   * @returns {Promise<number>} 清理的记录数
   */
  static async cleanupExpired() {
    const sql = `
      DELETE FROM email_verification_codes 
      WHERE expires_at < NOW() OR (is_used = TRUE AND created_at < DATE_SUB(NOW(), INTERVAL 1 DAY))
    `;
    
    try {
      const result = await query(sql);
      return result.affectedRows;
    } catch (error) {
      console.error('清理过期验证码失败:', error);
      throw error;
    }
  }

  /**
   * 获取邮箱验证码统计信息
   * @returns {Promise<Object>} 统计信息
   */
  static async getStats() {
    const sql = `
      SELECT 
        COUNT(*) as total_codes,
        COUNT(CASE WHEN is_used = TRUE THEN 1 END) as used_codes,
        COUNT(CASE WHEN expires_at < NOW() THEN 1 END) as expired_codes,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as today_codes,
        COUNT(CASE WHEN type = 'register' THEN 1 END) as register_codes,
        COUNT(CASE WHEN type = 'reset_password' THEN 1 END) as reset_codes
      FROM email_verification_codes
    `;

    try {
      const results = await query(sql);
      return results[0];
    } catch (error) {
      console.error('获取验证码统计失败:', error);
      throw error;
    }
  }

  /**
   * 使验证码失效
   * @param {number} id - 验证码ID
   * @returns {Promise<boolean>} 是否成功
   */
  static async markAsUsed(id) {
    const sql = 'UPDATE email_verification_codes SET is_used = TRUE WHERE id = ?';
    
    try {
      const result = await query(sql, [id]);
      return result.affectedRows > 0;
    } catch (error) {
      console.error('标记验证码为已使用失败:', error);
      throw error;
    }
  }

  /**
   * 检查验证码是否有效（未使用且未过期）
   * @returns {boolean} 是否有效
   */
  isValid() {
    const now = new Date();
    const expiresAt = new Date(this.expires_at);
    return !this.is_used && expiresAt > now;
  }

  /**
   * 转换为安全的JSON格式
   * @returns {Object} 安全的验证码信息
   */
  toSafeJSON() {
    return {
      id: this.id,
      email: this.email,
      type: this.type,
      expires_at: this.expires_at,
      is_used: this.is_used,
      created_at: this.created_at
    };
  }
}
